package cn.edu.lingnan.dao;

import cn.edu.lingnan.pojo.Position;
import cn.edu.lingnan.util.DBUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class PositionDaoMysqlImpl implements PositionDao {
    @Override
    public boolean newPosition(Position _member) {
        boolean flag=false;
        Connection conn = null;
        PreparedStatement prep = null;
        try {
            conn = DBUtil.getInstance().getConnection();
            prep = conn.prepareStatement("insert into position values(?,?,?)");
            prep.setString(1,_member.getSid());
            prep.setString(2,_member.getCid());
            prep.setString(3,_member.getPs());
            prep.executeUpdate();
            flag=true;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBUtil.getInstance().close(prep,conn);
        }
        return flag;
    }


    @Override
    public List<Position> findPositionAll() {
        List<Position> list = new ArrayList<>();
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            conn = DBUtil.getInstance().getConnection();
            st = conn.createStatement();
            rs = st.executeQuery("select * from position");
            while (rs.next()) {
                Position post=new Position();
                post.setSid(rs.getString(1));
                post.setCid(rs.getString(2));
                post.setPs(rs.getString(3));
                list.add(post);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBUtil.getInstance().close(rs,st,conn);
        }
        return list;
    }

    @Override
    public List<Position> findPositionBySid(String _sid) {
        List<Position> list = new ArrayList<>();
        Connection conn = null;
        PreparedStatement prep = null;
        ResultSet rs = null;
        try {
            conn = DBUtil.getInstance().getConnection();
            prep = conn.prepareStatement("select * from Position where sid=?");
            prep.setString(1,_sid);
            rs = prep.executeQuery();
            while (rs.next()) {
                Position post=new Position();
                post.setSid(rs.getString(1));
                post.setCid(rs.getString(2));
                post.setPs(rs.getString(3));
                list.add(post);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBUtil.getInstance().close(rs,prep,conn);
        }
        return list;
    }

    @Override
    public List<Position> findPositionByCid(String _cid) {
        List<Position> list = new ArrayList<>();
        Connection conn = null;
        PreparedStatement prep = null;
        ResultSet rs = null;
        try {
            conn = DBUtil.getInstance().getConnection();
            prep = conn.prepareStatement("select * from position where cid=?");
            prep.setString(1,_cid);
            rs = prep.executeQuery();
            while (rs.next()) {
                Position member=new Position();
                member.setSid(rs.getString(1));
                member.setCid(rs.getString(2));
                member.setPs(rs.getString(3));
                list.add(member);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBUtil.getInstance().close(rs,prep,conn);
        }
        return list;
    }

    @Override
    public List<Position> findPositionByPs(String _ps) {
        List<Position> list = new ArrayList<>();
        Connection conn = null;
        PreparedStatement prep = null;
        ResultSet rs = null;
        try {
            conn = DBUtil.getInstance().getConnection();
            prep = conn.prepareStatement("select * from position where ps like ?");
            prep.setString(1,"%" + _ps + "%");
            rs = prep.executeQuery();
            while (rs.next()) {
                Position member=new Position();
                member.setSid(rs.getString(1));
                member.setCid(rs.getString(2));
                member.setPs(rs.getString(3));
                list.add(member);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBUtil.getInstance().close(rs,prep,conn);
        }
        return list;
    }

    @Override
    public boolean updatePsBySidAndCid(Position _post) {
        boolean flag = false;
        Connection conn = null;
        PreparedStatement prep = null;
        try {
            conn = DBUtil.getInstance().getConnection();
            prep = conn.prepareStatement("update position set ps=? where sid=? AND cid=?");
            prep.setString(1, _post.getPs());
            prep.setString(2, _post.getSid());
            prep.setString(3, _post.getCid());
            prep.executeUpdate();
            if(prep.executeUpdate()==1){
                flag=true;
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBUtil.getInstance().close(prep, conn);
        }
        return flag;
    }


    @Override
    public boolean deletePositionBySidAndCid(String _sid, String _cid) {
        boolean flag=false;
        Connection conn = null;
        PreparedStatement prep = null;
        try {
            conn = DBUtil.getInstance().getConnection();
            prep = conn.prepareStatement("delete from position where sid=? AND cid=?");
            prep.setString(1, _sid);
            prep.setString(2, _cid);
            if(prep.executeUpdate()==1){
                flag=true;
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBUtil.getInstance().close(prep,conn);
        }
        return flag;
    }


}
